package com.tianqu.tms.www.statistics.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TmsTableNames;
import com.tianqu.tms.www.statistics.entity.TmsMarketRegisterDayCountDto;

public class TmsMarketRegisterDayCountDao extends TmsDao{

	public TmsMarketRegisterDayCountDao() throws TmsDaoException {
		super();
	}

	/**
	 * 获得统计开始日期
	 * @return
	 * @throws TmsDaoException
	 */	
	public TmsMarketRegisterDayCountDto getStartCountDate() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();
		
		try {
			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" countDate");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" ORDER BY countDate ASC LIMIT 1");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			// 获得第一条的统计日期
			if (rs.next()) {
				// 统计日期
				dto = new TmsMarketRegisterDayCountDto();
				dto.setCountDate(rs.getString("countDate"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 累计库存总数/累计库存总价
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsMarketRegisterDayCountDto getCumulativeStatisticalInfo() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();
		
		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" SUM(regNum) AS regNum,");
			sql.append(" SUM(price) AS price");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" ORDER BY countDate ASC LIMIT 1");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			if (rs.next()) {
				dto = new TmsMarketRegisterDayCountDto();
				dto.setRegNum(rs.getBigDecimal("regNum"));
				dto.setPrice(rs.getBigDecimal("price"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 实时在库车辆、空位数、车位使用率
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsMarketRegisterDayCountDto getRealTimeStatisticalInfo() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();
		
		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" onlineNum,");
			sql.append(" freeNum,");
			sql.append(" ratio");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" WHERE countDate = date_format(CURDATE(),'%Y-%m-%d')");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			if (rs.next()) {
				dto = new TmsMarketRegisterDayCountDto();
				dto.setOnlineNum(rs.getInt("onlineNum"));
				dto.setFreeNum(rs.getInt("freeNum"));
				dto.setRatio(rs.getBigDecimal("ratio"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 今天新增库存数量/今天新增库存总值
	 * @return
	 * @throws TmsDaoException
	 */	
	public TmsMarketRegisterDayCountDto getTodayNewInventory() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();
		
		try {
			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" regNum,");
			sql.append(" price");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" WHERE countDate = date_format(CURDATE(),'%Y-%m-%d') ");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			dto = new TmsMarketRegisterDayCountDto();

			if (rs.next()) {
				dto.setRegNum(rs.getBigDecimal("regNum"));
				dto.setPrice(rs.getBigDecimal("price"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 当月新增库存数量/当月新增库存总值/本月平均车位使用率
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsMarketRegisterDayCountDto getMonthNewInventory() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();

		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" SUM(regNum) AS regNum,");
			sql.append(" SUM(price) AS price,");
			sql.append(" AVG(ratio) AS ratio");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" WHERE countY = DATE_FORMAT(NOW(),'%Y') ");
			sql.append(" AND countM = DATE_FORMAT(NOW(),'%c') ");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			dto = new TmsMarketRegisterDayCountDto();

			if (rs.next()) {
				dto.setRegNum(rs.getBigDecimal("regNum"));
				dto.setPrice(rs.getBigDecimal("price"));
				dto.setRatio(rs.getBigDecimal("ratio"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 上一年度当前月份入库总量/上一年度同期累计新增总价/上一年度同期月平均车位使用率
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsMarketRegisterDayCountDto getLastYearTimeParkingRate() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();

		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" IFNULL(SUM(regNum), 0) AS regNum,");
			sql.append(" IFNULL(SUM(price), 0) AS price,");
			sql.append(" IFNULL(AVG(ratio), 0) AS ratio");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" where countY = date_format(DATE_SUB(NOW(), INTERVAL 1 YEAR), '%Y') ");
			sql.append(" and countM = DATE_FORMAT(NOW(),'%c') ");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			if (rs.next()) {
				dto.setRegNum(rs.getBigDecimal("regNum"));
				dto.setPrice(rs.getBigDecimal("price"));
				dto.setRatio(rs.getBigDecimal("ratio"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 上月入库总量/上月累计新增总价/上月平均车位使用率
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsMarketRegisterDayCountDto getLastMonthParkingRate() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();

		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" IFNULL(SUM(regNum), 0) AS regNum,");
			sql.append(" IFNULL(SUM(price), 0) AS price,");
			sql.append(" IFNULL(AVG(ratio), 0) AS ratio");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" WHERE countY = DATE_FORMAT(NOW(),'%Y')");
			sql.append(" AND countM = date_format(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%c')");
			sql.append(" AND countDate <= concat(countY,LPAD(countM, 2, 0), DATE_FORMAT(NOW(),'%d'))");
			sql.append(" GROUP BY countY, countM");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			if (rs.next()) {
				dto = new TmsMarketRegisterDayCountDto();
				dto.setRegNum(rs.getBigDecimal("regNum"));
				dto.setPrice(rs.getBigDecimal("price"));
				dto.setRatio(rs.getBigDecimal("ratio"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 第10个月内新增入库/第10个月内车位使用率
	 * @return
	 * @throws TmsDaoException
	 */	
	public TmsMarketRegisterDayCountDto getD10MonthNewStorageAndParkingRate() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsMarketRegisterDayCountDto dto = new TmsMarketRegisterDayCountDto();
		
		try {
			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" concat(tmrdc.countY, LPAD(tmrdc.countM, 2, 0)) as resDate,");
			sql.append(" IFNULL(SUM(regNum), 0) AS regNum,");
			sql.append(" IFNULL(AVG(ratio), 0) AS ratio");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_REGISTER_DAY_COUNT);
			sql.append(" tmrdc");
			sql.append(" where tmrdc.countM = date_format(now(),'%m') and tmrdc.countY = date_format (now(),'%Y')");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			dto = new TmsMarketRegisterDayCountDto();

			if (rs.next()) {
				dto.setResDate(rs.getString("resDate"));
				dto.setRegNum(rs.getBigDecimal("regNum"));
				dto.setRatio(rs.getBigDecimal("ratio"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
}
